﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Web;
using System.Web.UI;
using System.Web.UI.WebControls;

using System.Data;
using System.Data.SqlClient;
using System.Configuration;

using System.IO;
using NPOI.HSSF.UserModel;
using NPOI.HPSF;

namespace syc
{
    public partial class JinDu3_Report : System.Web.UI.Page
    {
        protected HSSFWorkbook hssfworkbook;
        protected int[] chesi = new int[20];
        protected int[] leiji = new int[20];
        protected int[] zhi = new int[12];
        protected void Page_Load(object sender, EventArgs e)
        {
            if (RiQi.Text == "")
            {
                RiQi.Text = DateTime.Today.ToString("yyyy年M月d日");
                RiQi2.Text = DateTime.Today.Year.ToString() + "年" + (DateTime.Today.Month).ToString() + "月1日";
                RiQi3.Text = DateTime.Today.Year.ToString() + "年1月1日";
            }
        }

        protected void WriteToFile()
        {
            DateTime d;
            if(!DateTime.TryParse(this.RiQi.Text,out d))
            {
                d=DateTime.Today;
            }
            Response.ContentType = "application/ms-excel";
            Response.AddHeader("content-disposition", "attachment; filename=\"" + d.ToShortDateString() + ".xls\"");
            hssfworkbook.Write(Response.OutputStream);
        }

        protected void InitializeWorkbook()
        {

            FileStream file = new FileStream(HttpContext.Current.Server.MapPath("报表模板/三分厂生产进度日报表.xls"), FileMode.Open, FileAccess.Read);

            hssfworkbook = new HSSFWorkbook(file);

            DocumentSummaryInformation dsi = PropertySetFactory.CreateDocumentSummaryInformation();
            dsi.Company = "陕西延长石油材料有限公司";
            hssfworkbook.DocumentSummaryInformation = dsi;

            SummaryInformation si = PropertySetFactory.CreateSummaryInformation();
            si.Subject = "报表";
            si.Title = "三分厂生产进度日报表";
            hssfworkbook.SummaryInformation = si;
        }

        protected void Button_Save_Click(object sender, EventArgs e)
        {
            InitializeWorkbook();
            HSSFSheet sheet1 = hssfworkbook.GetSheet("数据");
            sheet1.GetRow(1).GetCell(0).SetCellValue(this.RiQi.Text);
            Button_Query_Click(null, null);
            sheet1.GetRow(3).GetCell(1).SetCellValue(chesi[0]);
            sheet1.GetRow(3).GetCell(2).SetCellValue(chesi[1]);
            sheet1.GetRow(3).GetCell(3).SetCellValue(chesi[2]);
            sheet1.GetRow(3).GetCell(4).SetCellValue(chesi[3]);
            sheet1.GetRow(3).GetCell(5).SetCellValue(chesi[4]);
            sheet1.GetRow(3).GetCell(6).SetCellValue(chesi[5]);
            sheet1.GetRow(3).GetCell(7).SetCellValue(chesi[6]);
            sheet1.GetRow(3).GetCell(8).SetCellValue(chesi[7]);
            sheet1.GetRow(3).GetCell(9).SetCellValue(chesi[8]);
            sheet1.GetRow(3).GetCell(10).SetCellValue(chesi[9]);
            sheet1.GetRow(3).GetCell(11).SetCellValue(chesi[10]);
            sheet1.GetRow(3).GetCell(12).SetCellValue(chesi[11]);

            sheet1.GetRow(4).GetCell(1).SetCellValue(leiji[0]);
            sheet1.GetRow(4).GetCell(2).SetCellValue(leiji[1]);
            sheet1.GetRow(4).GetCell(3).SetCellValue(leiji[2]);
            sheet1.GetRow(4).GetCell(4).SetCellValue(leiji[3]);
            sheet1.GetRow(4).GetCell(5).SetCellValue(leiji[4]);
            sheet1.GetRow(4).GetCell(6).SetCellValue(leiji[5]);
            sheet1.GetRow(4).GetCell(7).SetCellValue(leiji[6]);
            sheet1.GetRow(4).GetCell(8).SetCellValue(leiji[7]);
            sheet1.GetRow(4).GetCell(9).SetCellValue(leiji[8]);
            sheet1.GetRow(4).GetCell(10).SetCellValue(leiji[9]);
            sheet1.GetRow(4).GetCell(11).SetCellValue(leiji[10]);
            sheet1.GetRow(4).GetCell(12).SetCellValue(leiji[11]);

            sheet1.GetRow(6).GetCell(2).SetCellValue(zhi[0]);
            sheet1.GetRow(6).GetCell(3).SetCellValue(zhi[2]);
            sheet1.GetRow(6).GetCell(5).SetCellValue(zhi[4]);

            sheet1.GetRow(7).GetCell(2).SetCellValue(zhi[1]);
            sheet1.GetRow(7).GetCell(3).SetCellValue(zhi[3]);
            sheet1.GetRow(7).GetCell(5).SetCellValue(zhi[5]);

            sheet1.GetRow(8).GetCell(2).SetCellValue(zhi[6]);
            sheet1.GetRow(8).GetCell(3).SetCellValue(zhi[8]);
            sheet1.GetRow(8).GetCell(5).SetCellValue(zhi[10]);

            sheet1.GetRow(9).GetCell(2).SetCellValue(zhi[7]);
            sheet1.GetRow(9).GetCell(3).SetCellValue(zhi[9]);
            sheet1.GetRow(9).GetCell(5).SetCellValue(zhi[11]);



            string op = (string)this.Session["OperatorName"];
            if (op !=null)
            {
                sheet1.GetRow(10).GetCell(11).SetCellValue(op);
            }
            sheet1.ForceFormulaRecalculation = true;
            WriteToFile();
            Response.End();
        }

        protected void Button_Query_Click(object sender, EventArgs e)
        {
            DateTime d1, d2, d3;
            if (!DateTime.TryParse(this.RiQi.Text, out d1))
            {
                this.Message.Text = "·请输入日期";
                return;
            }
            if (!DateTime.TryParse(this.RiQi2.Text, out d2))
            {
                this.Message.Text = "·请输入累计日期";
                return;
            }
            if (!DateTime.TryParse(this.RiQi3.Text, out d3))
            {
                this.Message.Text = "·请输入年累计日期";
                return;
            }

            SqlConnection Conn = new SqlConnection();
            Conn.ConnectionString = ConfigurationManager.ConnectionStrings["SCMConnectionString1"].ConnectionString;
            Conn.Open();
            SqlCommand Cmd = new SqlCommand();
            Cmd.Connection = Conn;
            Cmd.CommandText =
@"select
sum(车丝1) as 车丝1,
sum(车丝2) as 车丝2,
sum(车丝3) as 车丝3,
sum(车丝4) as 车丝4,
sum(车丝5) as 车丝5,
sum(车丝6) as 车丝6,
sum(车丝7) as 车丝7,
sum(车丝8) as 车丝8,
sum(车丝9) as 车丝9,
sum(车丝10) as 车丝10,
sum(车丝11) as 车丝11,
sum(车丝12) as 车丝12,
sum(车丝13) as 车丝13,
sum(车丝14) as 车丝14,
sum(车丝15) as 车丝15,
sum(车丝16) as 车丝16,
sum(车丝17) as 车丝17,
sum(车丝18) as 车丝18,
sum(车丝19) as 车丝19,
sum(车丝20) as 车丝20
from [生产进度3] where [日期]='"
+ d1.ToShortDateString()+"'";
            SqlDataReader Reader = Cmd.ExecuteReader();
            if (Reader.Read())
            {
                if (!Reader.IsDBNull(0))
                {
                    chesi[0] = Reader.GetInt32(0);
                    chesi[1] = Reader.GetInt32(1);
                    chesi[2] = Reader.GetInt32(2);
                    chesi[3] = Reader.GetInt32(3);
                    chesi[4] = Reader.GetInt32(4);
                    chesi[5] = Reader.GetInt32(5);
                    chesi[6] = Reader.GetInt32(6);
                    chesi[7] = Reader.GetInt32(7);
                    chesi[8] = Reader.GetInt32(8);
                    chesi[9] = Reader.GetInt32(9);
                    chesi[10] = Reader.GetInt32(10);
                    chesi[11] = Reader.GetInt32(11);
                    chesi[12] = Reader.GetInt32(12);
                    chesi[13] = Reader.GetInt32(13);
                    chesi[14] = Reader.GetInt32(14);
                    chesi[15] = Reader.GetInt32(15);
                    chesi[16] = Reader.GetInt32(16);
                    chesi[17] = Reader.GetInt32(17);
                    chesi[18] = Reader.GetInt32(18);
                    chesi[19] = Reader.GetInt32(19);
                }
            }
            Reader.Close();


            Cmd.CommandText =
@"select
sum(车丝1) as 车丝1,
sum(车丝2) as 车丝2,
sum(车丝3) as 车丝3,
sum(车丝4) as 车丝4,
sum(车丝5) as 车丝5,
sum(车丝6) as 车丝6,
sum(车丝7) as 车丝7,
sum(车丝8) as 车丝8,
sum(车丝9) as 车丝9,
sum(车丝10) as 车丝10,
sum(车丝11) as 车丝11,
sum(车丝12) as 车丝12,
sum(车丝13) as 车丝13,
sum(车丝14) as 车丝14,
sum(车丝15) as 车丝15,
sum(车丝16) as 车丝16,
sum(车丝17) as 车丝17,
sum(车丝18) as 车丝18,
sum(车丝19) as 车丝19,
sum(车丝20) as 车丝20
from [生产进度3] where [日期]>='"
+ d2.ToShortDateString() + "' and [日期]<='" + d1.ToShortDateString() +"'";
            Reader = Cmd.ExecuteReader();
            if (Reader.Read())
            {
                if (!Reader.IsDBNull(0))
                {
                    leiji[0] = Reader.GetInt32(0);
                    leiji[1] = Reader.GetInt32(1);
                    leiji[2] = Reader.GetInt32(2);
                    leiji[3] = Reader.GetInt32(3);
                    leiji[4] = Reader.GetInt32(4);
                    leiji[5] = Reader.GetInt32(5);
                    leiji[6] = Reader.GetInt32(6);
                    leiji[7] = Reader.GetInt32(7);
                    leiji[8] = Reader.GetInt32(8);
                    leiji[9] = Reader.GetInt32(9);
                    leiji[10] = Reader.GetInt32(10);
                    leiji[11] = Reader.GetInt32(11);
                    leiji[12] = Reader.GetInt32(12);
                    leiji[13] = Reader.GetInt32(13);
                    leiji[14] = Reader.GetInt32(14);
                    leiji[15] = Reader.GetInt32(15);
                    leiji[16] = Reader.GetInt32(16);
                    leiji[17] = Reader.GetInt32(17);
                    leiji[18] = Reader.GetInt32(18);
                    leiji[19] = Reader.GetInt32(19);
                }
            }
            Reader.Close();


            Cmd.CommandText =
@"declare @i1 int;
declare @i2 int;
declare @i3 int;
declare @i4 int;
declare @i5 int;
declare @i6 int;
declare @i7 int;
declare @i8 int;
declare @i9 int;
declare @i10 int;
declare @i11 int;
declare @i12 int;
select @i1=sum(磁粉碳伤合格),@i2=sum(磷化支数) from [生产进度3] where [产品类别ID]=1 and [日期]='"+d1.ToShortDateString()+@"';
select @i3=sum(磁粉碳伤合格),@i4=sum(磷化支数) from [生产进度3] where [产品类别ID]=1 and [日期]>='"+ d2.ToShortDateString() + "' and [日期]<='" + d1.ToShortDateString() +@"';
select @i5=sum(磁粉碳伤合格),@i6=sum(磷化支数) from [生产进度3] where [产品类别ID]=1 and [日期]>='" + d3.ToShortDateString() + "' and [日期]<='" + d1.ToShortDateString() + @"';
select @i7=sum(磁粉碳伤合格),@i8=sum(磷化支数) from [生产进度3] where [产品类别ID]=2 and [日期]='" + d1.ToShortDateString() + @"';
select @i9=sum(磁粉碳伤合格),@i10=sum(磷化支数) from [生产进度3] where [产品类别ID]=2 and [日期]>='" + d2.ToShortDateString() + "' and [日期]<='" + d1.ToShortDateString() + @"';
select @i11=sum(磁粉碳伤合格),@i12=sum(磷化支数) from [生产进度3] where [产品类别ID]=2 and [日期]>='" + d3.ToShortDateString() + "' and [日期]<='" + d1.ToShortDateString() + @"';
select @i1,@i2,@i3,@i4,@i5,@i6,@i7,@i8,@i9,@i10,@i11,@i12;";


            Reader = Cmd.ExecuteReader();
            if (Reader.Read())
            {
                if (!Reader.IsDBNull(0))
                {
                    zhi[0] = Reader.GetInt32(0);
                    zhi[1] = Reader.GetInt32(1);
                    zhi[2] = Reader.GetInt32(2);
                    zhi[3] = Reader.GetInt32(3);
                    zhi[4] = Reader.GetInt32(4);
                    zhi[5] = Reader.GetInt32(5);
                }
                if (!Reader.IsDBNull(6))
                {
                    zhi[6] = Reader.GetInt32(6);
                    zhi[7] = Reader.GetInt32(7);
                    zhi[8] = Reader.GetInt32(8);
                    zhi[9] = Reader.GetInt32(9);
                    zhi[10] = Reader.GetInt32(10);
                    zhi[11] = Reader.GetInt32(11);
                }
            }
            Reader.Close();

            Conn.Close();
            Reader.Dispose();
            Cmd.Dispose();
            Conn.Dispose();
        }
    }
}
